const { query } = require('../config/db');
const bcrypt = require('bcrypt');

const userModel = {
  /**
   * 根据手机号查找用户
   */
  async findByPhone(phone) {
    const sql = `
      SELECT u.*, 
             a1.name as province_name, 
             a2.name as city_name, 
             a3.name as county_name
      FROM users u
      LEFT JOIN areas a3 ON u.area_id = a3.id
      LEFT JOIN areas a2 ON a3.parent_id = a2.id
      LEFT JOIN areas a1 ON a2.parent_id = a1.id
      WHERE u.phone = ?
    `;
    const results = await query(sql, [phone]);
    return results[0];
  },

  /**
   * 根据ID查找用户
   */
  async findById(id) {
    const sql = `
      SELECT u.*, 
             a1.name as province_name, 
             a2.name as city_name, 
             a3.name as county_name
      FROM users u
      LEFT JOIN areas a3 ON u.area_id = a3.id
      LEFT JOIN areas a2 ON a3.parent_id = a2.id
      LEFT JOIN areas a1 ON a2.parent_id = a1.id
      WHERE u.id = ?
    `;
    const results = await query(sql, [id]);
    return results[0];
  },

  /**
   * 创建新用户
   */
  async create(userData) {
    const { phone, password, name, id_card, area_id } = userData;
    
    // 密码加密
    const salt = await bcrypt.genSalt(10);
    const hashedPassword = await bcrypt.hash(password, salt);
    
    const sql = `
      INSERT INTO users (phone, password, name, id_card, area_id)
      VALUES (?, ?, ?, ?, ?)
    `;
    
    const result = await query(sql, [
      phone,
      hashedPassword,
      name || null,
      id_card || null,
      area_id || null
    ]);
    
    return result.insertId;
  },

  /**
   * 验证密码
   */
  async verifyPassword(plainPassword, hashedPassword) {
    return await bcrypt.compare(plainPassword, hashedPassword);
  },

  /**
   * 更新用户信息
   */
  async update(id, updateData) {
    const fields = [];
    const values = [];
    
    // 动态构建更新字段
    if (updateData.name !== undefined) {
      fields.push('name = ?');
      values.push(updateData.name);
    }
    if (updateData.id_card !== undefined) {
      fields.push('id_card = ?');
      values.push(updateData.id_card);
    }
    if (updateData.area_id !== undefined) {
      fields.push('area_id = ?');
      values.push(updateData.area_id);
    }
    if (updateData.password !== undefined) {
      const salt = await bcrypt.genSalt(10);
      const hashedPassword = await bcrypt.hash(updateData.password, salt);
      fields.push('password = ?');
      values.push(hashedPassword);
    }
    
    if (fields.length === 0) {
      throw new Error('没有可更新的字段');
    }
    
    values.push(id);
    const sql = `UPDATE users SET ${fields.join(', ')} WHERE id = ?`;
    
    const result = await query(sql, values);
    return result.affectedRows > 0;
  },

  /**
   * 更新最后登录时间
   */
  async updateLastLogin(id) {
    const sql = 'UPDATE users SET last_login_at = NOW() WHERE id = ?';
    await query(sql, [id]);
  },

  /**
   * 更新用户状态
   */
  async updateStatus(id, status) {
    const sql = 'UPDATE users SET status = ? WHERE id = ?';
    const result = await query(sql, [status, id]);
    return result.affectedRows > 0;
  },

  /**
   * 检查手机号是否已注册
   */
  async isPhoneExists(phone) {
    const sql = 'SELECT COUNT(*) as count FROM users WHERE phone = ?';
    const results = await query(sql, [phone]);
    return results[0].count > 0;
  },

  /**
   * 获取用户列表（分页）
   */
  async getList(page = 1, limit = 20, filters = {}) {
    const offset = (page - 1) * limit;
    const conditions = [];
    const values = [];
    
    // 构建查询条件
    if (filters.phone) {
      conditions.push('u.phone LIKE ?');
      values.push(`%${filters.phone}%`);
    }
    if (filters.name) {
      conditions.push('u.name LIKE ?');
      values.push(`%${filters.name}%`);
    }
    if (filters.status) {
      conditions.push('u.status = ?');
      values.push(filters.status);
    }
    if (filters.area_id) {
      conditions.push('u.area_id = ?');
      values.push(filters.area_id);
    }
    
    const whereClause = conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : '';
    
    // 查询总数
    const countSql = `SELECT COUNT(*) as total FROM users u ${whereClause}`;
    const countResult = await query(countSql, values);
    const total = countResult[0].total;
    
    // 查询数据
    const sql = `
      SELECT u.id, u.phone, u.name, u.id_card, u.status, u.last_login_at, 
             u.created_at, u.updated_at,
             a1.name as province_name, 
             a2.name as city_name, 
             a3.name as county_name
      FROM users u
      LEFT JOIN areas a3 ON u.area_id = a3.id
      LEFT JOIN areas a2 ON a3.parent_id = a2.id
      LEFT JOIN areas a1 ON a2.parent_id = a1.id
      ${whereClause}
      ORDER BY u.created_at DESC
      LIMIT ? OFFSET ?
    `;
    
    values.push(limit, offset);
    const results = await query(sql, values);
    
    return {
      list: results,
      total,
      page,
      limit,
      pages: Math.ceil(total / limit)
    };
  }
};

module.exports = userModel;

